﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;    //数据库头文件
using System.Data.Sql;          //数据库头文件


namespace DATA_operate_
{
    /// <summary>
    ///DATA_operate 的摘要说明
    /// </summary>
    /// 
    public class DATA_operate
    {
        public SqlConnection objConnection;


        public DATA_operate()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        //使用类操作必须首先修改打开数据库函数之中的信息
        public SqlConnection databaseopen()
        {

            string strConnection = "Integrated Security=SSPI;initial catalog=mei_design;Server=USER-CQ3KP08JVH\\SQLEXPRESS;";//initial catalog=design;Server=USER-CQ3KP08JVH\\SQLEXPRESS;"必须修改
            //Integrated Security：安全模式（sspi----windows身份验证--无账号密码)         initial catalog:数据库名称    Server：服务名称
            strConnection += "Connect Timeout=5";   //设置超时
            objConnection = new SqlConnection(strConnection);
            objConnection.Open();
            return objConnection;

        }
        //--------------------------------------------------------------------------


        //关闭数据库222222222222222222222222222
        public void databaseclose()
        {
            objConnection.Close();
        }
        //--------------------------------------------------------------------------

        //sql语句执行33333333333333333333333333
        /*格式---sqlcmd(sqlsel)*/
        public SqlCommand sqlcmd(string str_sql)
        {
            //databaseopen();   //打开数据库
            databaseopen();
            SqlCommand com = new SqlCommand(str_sql, objConnection);//新建sql查询命令
            return com;//返回查询结果
        }
        //好像挺有用的。又加了一个
        public bool Is_sqlcmd_success(SqlCommand com)
        {
            if (com.ExecuteNonQuery() > 0)
            {
                return true;    //sql成功
            }
            else
                return false;  //sql失败
        }
        public bool Is_sqlcmd_success(SqlCommand com,int sql_type)
        {//sql_type =1
         
            SqlDataReader reader;
            reader = com.ExecuteReader();   //判断返回
            bool Is_succeed = reader.Read();
            if (Is_succeed)                  //已经存在
            {
                return true;
            }
            else                         //不存在
            {
                return false;
            }
        }
        //--------------------------------------------------------------------------



        //数据添加44444444444444444444444444444
        //dataadd(表名，列名，添加的信息)*****多条（添加的信息）用，好隔开
        //有选择的添加格式insert into _teacher_class(_cid,_tid) values('0005','22012000010004 ')
        //例如:dataadd("admin", "aid,apwd,", "sdsdsd,222");
        public bool dataadd(string str_table, string str_row, string str_addinfo)
        {
            //为str_addinfo添加单引号字符
            string[] arr = str_addinfo.Split(',');
            string b = string.Empty;
            for (int i = 0; i < arr.Length; i++)
            {
                b += string.Format(",'{0}'", arr[i]);
            }
            b = b.Substring(1);
            //字符处理

            string add_sql = "Insert Into " + str_table + "(" + str_row + ") values(" + b + ")"; //格式化数据添加语句
            if (sqlcmd(add_sql).ExecuteNonQuery() > 0)
            {
                return true;    //添加成功
            }
            else
                return false;  //添加失败
        }
        //默认的添加格式insert into _teacher_class values('0005','22012000010004 ')
        //例如string addinfo = classid + "," + teacherid;     data_operate_in_class_add.dataadd("_teacher_class", addinfo);
        public bool dataadd(string str_table, string str_addinfo)
        {
            //为str_addinfo添加单引号字符
            string[] arr = str_addinfo.Split(',');
            string b = string.Empty;
            for (int i = 0; i < arr.Length; i++)
            {
                b += string.Format(",'{0}'", arr[i]);
            }
            b = b.Substring(1);
            //字符处理

            string add_sql = "Insert Into " + str_table + "  values(" + b + ")"; //格式化数据添加语句
            if (sqlcmd(add_sql).ExecuteNonQuery() > 0)
            {
                return true;    //添加成功
            }
            else
                return false;  //添加失败
        }

        //--------------------------------------------------------------------------




        //数据删除5555555555555555555555555555
        //datadelete(表名，列名，匹对的条件信息)
        //删除该行的所有
        public bool datadelete(string str_table, string str_row, string str_condition_info)
        {
            string delete_sql = "delete from " + str_table + " where " + str_row + "= '" + str_condition_info +"'"; //格式化数据删除语句
            //delete_sql = "delete from table where row='123' and row='1233'";
            if (sqlcmd(delete_sql).ExecuteNonQuery() > 0)
            {
                return true;    //删除成功
            }
            else
                return false;  //删除失败
        }
        public bool datadelete(string str_table, string str_row1, string str_condition_info1, string str_row2, string str_condition_info2)
        {
            string delete_sql = "delete from " + str_table + " where " + str_row1 + "= '" + str_condition_info1 + "' and " + str_row2 + " = '" + str_condition_info2+"'"; //格式化数据删除语句
            //delete_sql = "delete from table where row='123' and row='1233'";
            if (sqlcmd(delete_sql).ExecuteNonQuery() > 0)
            {
                return true;    //删除成功
            }
            else
                return false;  //删除失败
        }
        //--------------------------------------------------------------------------



        //数据修改6666666666666666666666666666666
        //dataupdate(表名，要修改的列名，修改的信息，修改条件的列名，修改条件列名的信息)
        //例如:
        public bool Data_update(string str_table, string str_update_row, string str_update_info, string str_condition_row, string str_condition_info)
        {
            string update_sql = "update  " + str_table + " set " + str_update_row + "= '" + str_update_info + "' where " + str_condition_row + " = '" + str_condition_info + "'"; //格式化数据修改语句
            //update str_table set str_update_row = 'str_update_info' where str_condition_row = 'str_condition_info'
            if (sqlcmd(update_sql).ExecuteNonQuery() > 0)
            {
                return true;    //修改成功
            }
            else
                return false;  //修改失败
        }
        public bool Data_update(string str_table, string str_update_row, string str_update_info, string str_condition_row1, string str_condition_info1, string str_condition_row2, string str_condition_info2)
        {
            string update_sql = "update  " + str_table + " set " + str_update_row + "= '" + str_update_info + "' where " + str_condition_row1 + " = '" + str_condition_info1 + "' and " + str_condition_row2 + " ='" + str_condition_info2+"'"; //格式化数据修改语句
            //update str_table set str_update_row = 'str_update_info' where str_condition_row1 = 'str_condition_info1' and str_condition_row2 = 'str_condition_info2'
            if (sqlcmd(update_sql).ExecuteNonQuery() > 0)
            {
                return true;    //修改成功
            }
            else
                return false;  //修改失败
        }
        //--------------------------------------------------------------------------



        //数据读取7777777777777777777777777777777777
        //数据读取   无条件读取   读取全部
        //dataread(表名，查询的列名)       多个列名用逗号隔开
        //是否是特殊列 
        //dataread(表名，查询的列名,是否是特殊列)
        //如：select MAX(_mHid) from _main_homework      ----MAX(_mHid)不是正常的列读取的时候要 con.ExecuteScalar() as string;这样读取
        //string str = publishinfo.dataread( "MAX(_mHid)","_main_homework");
        public string dataread(string str_row, string str_table, bool Is_special_row)
        {
            string str = "";
            if (Is_special_row)   //特殊列滴  如publishinfo.dataread( "MAX(_mHid)","_main_homework",true);
            {
                string read_sql = "select " + str_row + " from " + str_table; //格式化数据修改语句
                SqlCommand con = sqlcmd(read_sql);
                str = con.ExecuteScalar() as string;
                databaseclose();//关闭数据库
            }
            else     //正常列dataread("admin","aid,password,aname",bool);
            {
                string read_sql = "select " + str_row + " from " + str_table; //格式化数据修改语句
                SqlCommand con = sqlcmd(read_sql);
                SqlDataReader dr = con.ExecuteReader();
                while (dr.Read())
                {
                    str = dr[str_row] + "";    //获得字段
                }
                databaseclose();//关闭数据库
            }
           
            return str;
        }
        //条件读取
        //dataread(表名，查询的列名,条件判断的列名，条件判断的信息)       多个列名用逗号隔开
        //例如:dataread("admin","aid,password,aname","aid","123012010096");
        /*例子   TextBox2.Text = data_operate_in_class_add.dataread("_teacher", "_Tname", "_Tid", DropDownList1.SelectedItem.Text);   //将返回_Tname教师的名字
         */
        public string dataread(string str_table, string str_row, string str_condition_row, string str_conditioninfo)
        {
            string read_sql = "select " + str_row + " from " + str_table + " where " + str_condition_row + "= '" + str_conditioninfo +"'"; //格式化数据修改语句
            //raed_sql="select str_row from str_table where str_condition_row= 'str_conditioninfo';

            SqlCommand con = sqlcmd(read_sql);
            SqlDataReader dr = con.ExecuteReader();
            string str="";
            while(dr.Read())
            {              
                str = dr[str_row] + "";    //获得字段
            }
            databaseclose();//关闭数据库
            return str;
        }
        //2个判断的也经常用到。再写个
        public string dataread(string str_table, string str_row, string str_condition_row1, string str_conditioninfo1,string str_condition_row2, string str_conditioninfo2)
        {
            string read_sql = "select " + str_row + " from " + str_table + " where " + str_condition_row1 + "= '" + str_conditioninfo1 + "' and " + str_condition_row2 + " = '" + str_conditioninfo2+"'"; //格式化数据修改语句
            //raed_sql="select str_row from str_table where str_condition_row= 'str_conditioninfo';

            SqlCommand con = sqlcmd(read_sql);
            SqlDataReader dr = con.ExecuteReader();
            string str = "";
            while (dr.Read())
            {
                str = dr[str_row] + "";    //获得字段
            }
            databaseclose();//关闭数据库
            return str;
        }
        //--------------------------------------------------------------------------
        


        //条件查询    判断是否存在
        public bool Is_exist(string table, string str_row, string str_condition_row)
        {
            string sql = "SELECT * FROM " + table + " WHERE " + str_row + " = " + "'" + str_condition_row + "'" ;   //构造sql语句
            //select * from table where row_name = 'name'
            SqlDataReader reader;
            SqlCommand com = sqlcmd(sql);     //SQL查询
            reader = com.ExecuteReader();   //判断返回
            bool Is_succeed = reader.Read();
            if (Is_succeed)                  //已经存在
            {
                return true;
            }
            else                         //不存在
            {
                return false;
            }
        }
       
        //条件查询    判断是否存在-----------------------------



        //登录验证好像很经常用到。再加一个
        //****************************表名     用户名字段名     输入的用户名     密码字段名    输入的密码***********************************
        //例子：   if (data_operate_in_login.Is_succeed_login("_student", "_Sid", TextBox1.Text, "_Spwd", TextBox2.Text))  //进行登录验证
        //          Page.ClientScript.RegisterStartupScript(this.GetType(), "right", "window.alert('登录成功。你是学生');", true);
        public bool Is_succeed_login(string table,string row_name,string name,string row_password,string password)
        {
            string sql = "SELECT * FROM " + table + " WHERE " + row_name + " = " +"'" + name + "'" + " and " + row_password + " = " + "'" + password + "'";   //构造sql语句
            //select * from table where row_name = 'name' and row_password = 'password'
            SqlDataReader reader;      
            SqlCommand com=sqlcmd(sql);     //SQL查询
            reader = com.ExecuteReader();   //判断返回
            bool Is_succeed = reader.Read();
            if(Is_succeed)                  //用户名和密码相符   验证通过
            {
                return true;
            }
            else                         //用户名和密码不相符   验证失败
            {
                return false;
            }
        }
        //---------------------------------------------------------------------------------------------------------------------------------



        //数据绑定
        //gridview数据绑定
        public void Data_bind(string sql, GridView gridview,string datakey)
        {
            
            SqlDataAdapter da = new SqlDataAdapter(sql, databaseopen());
            DataSet ds = new DataSet();
            da.Fill(ds);
            gridview.DataSource = ds.Tables[0];
            gridview.DataKeyNames = new string[] { datakey };
            gridview.DataBind();
        }
        public void Data_bind(string sql, DropDownList dropdownlist, string datakey)
        {

            SqlDataAdapter da = new SqlDataAdapter(sql, databaseopen());
            DataSet ds = new DataSet();
            da.Fill(ds);

           // dropdownlist.DataSource = ds.Tables["Table1"].DefaultView;
           // dropdownlist.DataValueField = datakey;//dropdownlist的Value的字段



            dropdownlist.DataSource = ds.Tables[0];
            dropdownlist.DataValueField = datakey;
            dropdownlist.DataBind();
        }




        /*钟大侠封装。版权所有*/
        /***********************数据库封装操作***************************************/
    }
}
